#!/usr/bin/env python
# coding:utf-8


import time
import logging
from copy import deepcopy
from utils.mysqlutils import MysqlUtils
from datatabletransfer import DataTableTransfer
from fieldsmap import *


class InstitutionsCompany(DataTableTransfer):
    """
    公募基金公司：机构资料表(TQ_COMP_INFO) --> 机构信息表(org_basic_info)
    全量导入，对于名称重复导入不了或者名称更新失败的不作任何处理
    """

    # 待插入的数据查询SQL
    QUERY_SOURCE = ('''select COMPCODE as source_id from TQ_COMP_INFO where ISVALID=1 ;''')
    QUERY_TARGET = ('''select replace(source_id, 'FD', '') as source_id from org_basic_info where status<>'9' and come_source = 'zh-data' 
    and source_id is not null and source_id <> '' ;''')

    TABLE = 'org_basic_info'  # 待插入的表名
    SOURCE_ID = {'source_id': False}  # 被关联表的主键, 源表的唯一索引
    COLUMN_ID = 'org_uni_code'  # 更新主键

    MAP_FIELDS = InstitutionsCompanyMap
    MAP_VALUES = InstitutionsCompanyValueMap


class FundSecurities(DataTableTransfer):
    """
    证券内码表(TQ_OA_STCODE) --> 证券基本信息表(sec_basic_info)
    """
    except_condition = (1030000749, 1030001708, 1030001867, 1030001866, 1030000684, 1030002785, 1030002786, 1030002404
                        , 1030001399, 1030000824, 1030001996, 1030001454, 1030001981, 1030005376, 1030005353, 1030004905, 1030001165
                        , 1030005367, 1030005497, 1030001096, 1030001489, 1030002621, 1030002622, 1030004274, 1030004275, 1030002069
                        , 1030001406, 1030001282, 1030002144, 1030002707, 1030002721, 1030002119, 1030009735, 1030002962, 1030001920
                        , 1030001958, 1030001970, 1030001919, 1030002549, 1030002550, 1030001097, 1030000271, 1030009386, 1030001378)
    except_condition_str = ','.join(map(lambda e: str(e), except_condition))

    # 待插入的数据查询SQL
    QUERY_SOURCE = ('''select a.SECURITYID as source_id from TQ_OA_STCODE a 
    inner join TQ_FD_BASICINFO b on a.SECURITYID=b.SECURITYID and a.SETYPE in (301, 302) 
    and a.ISVALID=1 where a.SECURITYID not in ({0}) ;'''.format(except_condition_str))
    QUERY_TARGET = ('''select replace(source_id,'FD','') as source_id from sec_basic_info 
    where sec_type = 1004003 and come_source = 'zh-data' and replace(source_id,'FD','') not in ({0});'''.format(except_condition_str))

    TABLE = 'sec_basic_info'  # 待插入的表名
    SOURCE_ID = {'source_id': False}  # 被关联表的主键, 源表的唯一索引
    COLUMN_ID = 'sec_uni_code'  # 更新主键

    MAP_FIELDS = FundSecuritiesMap
    MAP_VALUES = FundSecuritiesValueMap


class FundInformation(DataTableTransfer):
    """
    基金发行与上市(TQ_FD_ISSUE)/基金基本信息(TQ_FD_BASICINFO) --> 基金基本信息表(fund_basic_info)
    """
    QUERY_SOURCE = '''select  a.SECURITYID as sec_uni_code from TQ_FD_BASICINFO a 
    inner join TQ_FD_ISSUE b on a.SECURITYID=b.SECURITYID and b.ISSUETYPE=1;'''

    QUERY_TARGET = '''select replace(b.source_id,'FD','') as sec_uni_code
     from fund_basic_info f join sec_basic_info b on f.sec_uni_code = b.sec_uni_code where  f.status<>'9' ;'''
    # 待插入的数据查询SQL
    # QUERY_SQL = ('''select concat('FD',a.SECURITYID) as SECURITYID, a.UPDATEDATE, a.FDNAME, a.FDSNAME, a.FDTYPE,
    # a.FDMETHOD,a.INVESTSTYLE, a.KEEPERNAME, a.TRUSTEENAME, a.EXSITPERIOD, a.OPERATEPERIOD, a.OPERATEPERIODUNIT,
    # b.ISSUEPARVALUE, b.ISSUEMODE, b.UNITISSPRICE, b.UNITISSFEE, b.CUR, b.EXPISSTOTSHARE, b.ISSBEGDATE, b.ISSENDDATE,
    # cast(a.ISSTAT as char(1)) ISSTAT, cast(a.ISVALID as char(1)) ISVALID, b.SUBTYPE, b.PAYTYPE, b.SPONSUBPLAAMT,
    # b.INSUSUBPLAAMT, b.PUBSUBPLAAMT, b.PUBSUBTOTSHARE, b.SUBRTO,b.EFFFREFD,
    # case when a.FDMETHOD='封闭式基金' then b.UNITTOTSHARE when b.ISINTE=1 then b.TOTALFFERFD else null end TOTAL_COLL_SHARE,
    # b.ISSUESUCRATE, b.EFFSUBNUM, case when b.ISINTE=0 then b.TOTALFFERFD else 0 end TOTALFFERFD,b.OFFERINTE,
    # b.SPOSUBTOTSHARE, a.FOUNDDATE,a.EXISTENDDATE, a.OUTSUBBEGDATE, a.OUTSUBENDDATE,a.INSUBBEGDATE,a.INREDENDDATE,
    # a.SUBCONFDATE, a.REDCONFDATE,a.REDPAYDATE, a.LISTDATE,a.EXCHANGE, b.LISTCIRCAMT,a.NAVPUBTYPE,a.INVESTGOAL,
    # a.INVRULE,a.INVESTRANGE, a.RISKTYPE, a.INVESTPOLICY,a.DECISIONRULE, b.HUGEREDEPLAN, a.DISTRIBUTPRIN,a.MEMO
    # from TQ_FD_BASICINFO a inner join TQ_FD_ISSUE b on a.SECURITYID=b.SECURITYID and b.ISSUETYPE=1 limit %s, 1000;''')

    TABLE = 'fund_basic_info'  # 待插入的表名
    SOURCE_ID = {'sec_uni_code':False,}  # 被关联表的主键, 源表与目标表的关联字段
    COLUMN_ID = 'sec_uni_code'  # 更新主键

    MAP_FIELDS = FundInfoMap
    MAP_VALUES = FundInfoValueMap
    DEFAULT_VALUE = {
        'operate_period': None,
        'unit_par_value': None,
        'issue_price': None,
        'issue_fee': None,
        'coll_amut_upper': None,
        'spon_sub_pla_amt': None,
        'insu_sub_pla_amt': None,
        'pub_sub_pla_amt': None,
        'pub_sub_tot_share': None,
        'issue_sub_ratio': None,
        'issue_frozen_capital': None,
        'issue_success_rate': None,
        'valid_sub_num': None,
        'valid_sub_share': None,
        'coll_val_inti': None,
        'total_coll_share': None,
        'initiator_sub_share': None,
        'list_share': None,
    }
    DATETIME = {
        'announcement_date': None,
        'iss_beg_date': None,
        'iss_end_date': None,
        'fund_found_date': None,
        'fund_end_date': None,
        'out_app_start_date': None,
        'out_redem_start_date': None,
        'in_app_start_date': None,
        'in_redem_start_date': None,
        'fund_list_date': None,
    }


class FundBenchmark(DataTableTransfer):
    """
    基金业绩比较基准(TQ_FD_BENCHMARK) --> 基金业绩比较基准(fund_benchmark)
    """
    QUERY_SOURCE = '''select SECURITYID as sec_uni_code,BEGINDATE as begin_date from TQ_FD_BENCHMARK;'''

    QUERY_TARGET = '''select replace(b.source_id,'FD','') as sec_uni_code, replace(f.begin_date,'-','') as begin_date
       from fund_benchmark f join sec_basic_info b on f.sec_uni_code = b.sec_uni_code where  f.status<>'9' ;'''
    # 待插入的数据查询SQL
      # QUERY_SQL = ('''select concat('FD',SECURITYID) as SECURITYID, BEGINDATE, ENDDATE, PERBENMARK, YIEPERIOD, ISVALID,
    # MEMO from TQ_FD_BENCHMARK limit %s, 1000;''')

    TABLE = 'fund_benchmark'  # 待插入的表名
    SOURCE_ID = {'sec_uni_code':False, 'begin_date':False}  # 被关联表的主键, 源表与目标表的关联字段
    COLUMN_ID = 'id'  # 更新主键

    MAP_FIELDS = FundBenchMarkMap
    MAP_VALUES = FundBenchMarkValueMap
    DATETIME = {
        'begin_date': None,
        'end_date': None,
    }


class FundRateInfo(DataTableTransfer):
    """
    基金费率变动(TQ_FD_RATECHG) --> 基金费率(fund_rate_info)
    """
    QUERY_SOURCE = '''select SECURITYID as sec_uni_code,PUBLISHDATE as announcement_date,RATENAME2 as rate_type2,NUMBER as serial_number
    from TQ_FD_RATECHG  where RATENAME2 in ( '0101','0102','0201','0301','0401','0402','0403','0404','0405','0501','0502','0505','0506',
    '0509','0510','0512','0513','0514','0515','0516','0601','0604','0605','0607','1101','1301','1401') ;'''

    QUERY_TARGET = '''select replace(b.source_id,'FD','') as sec_uni_code, replace(f.announcement_date,'-','') as announcement_date,rate_type2,
    serial_number  from fund_rate_info f join sec_basic_info b on f.sec_uni_code = b.sec_uni_code where  f.status<>'9' ;'''
    # 待插入的数据查询SQL
    # QUERY_SQL = ('''select concat('FD',SECURITYID) as SECURITYID,PUBLISHDATE,BEGINDATE,ENDDATE,RATENAME1,RATENAME2,
    # NUMBER,COSTCALCMODE,CUR, RATECOSTUNIT,RATEMINCOST,RATERESH,RATEMAXCOST,APPMINAMT,APPAMTRESH,APPMAXAMT,HOLDPERUNIT,
    # HOLDMINPERIOD,HOLDPERRESH,HOLDMAXPERIOD,APPOBJECT,MEMO,ISVALID from TQ_FD_RATECHG
    # where RATENAME2 in ( '0101','0102','0201','0301','0401','0402','0403','0404','0405','0501','0502','0505','0506',
    # '0509','0510','0512','0513','0514','0515','0516','0601','0604','0605','0607','1101','1301','1401')
    # limit %s, 1000;''')

    TABLE = 'fund_rate_info'  # 待插入的表名
    SOURCE_ID = {'sec_uni_code':False, 'announcement_date':False, 'rate_type2':True, 'serial_number':False}  # 被关联表的主键, 源表与目标表的关联字段
    COLUMN_ID = 'id'  # 更新主键

    MAP_FIELDS = FundRateMap
    MAP_VALUES = FundRateValueMap
    DEFAULT_VALUE = {
        'app_max_amount': None,
        'app_min_amount': None,
        'hold_min_period': None,
        'rate_min_cost': None,
        'hold_max_period': None,
    }
    DATETIME = {
        'announcement_date': None,
        'begin_date': None,
        'end_date': None,
    }


class ClosedFundInformation(DataTableTransfer):
    """
    基金发行与上市(TQ_FD_ISSUE) --> 封闭基金基本信息表(fund_closed_basic_info)
    """
    QUERY_SOURCE = '''select  SECURITYID as sec_uni_code from TQ_FD_ISSUE where ISSUETYPE=2;'''

    QUERY_TARGET = '''select replace(b.source_id,'FD','') as sec_uni_code
         from fund_closed_basic_info f join sec_basic_info b on f.sec_uni_code = b.sec_uni_code where  f.status<>'9' ;'''
    # 待插入的数据查询SQL
    # QUERY_SQL = ('''select concat('FD',SECURITYID) as SECURITYID, PUBLISHDATE,NTLETPUBDATE,ISSBEGDATE,ISSENDDATE,
    # BEISSTOTSHARE,UNITTOTSHARE,UNITISSFEE,EXPORATIO,RECORDDATE,EXRIGHTBASDATE,LISTDATE,EXCHANGE,LISTCIRCAMT,
    # SPONSUBPLAAMT,INSUSUBPLAAMT, PUBSUBPLAAMT,PUBSUBTOTSHARE from TQ_FD_ISSUE where ISSUETYPE=2 limit %s, 1000;''')

    TABLE = 'fund_closed_basic_info'  # 待插入的表名
    SOURCE_ID = {'sec_uni_code':False,}
    COLUMN_ID = 'sec_uni_code'  # 更新主键

    MAP_FIELDS = ClosedFundInfoMap
    MAP_VALUES = ClosedFundInfoValueMap
    DEFAULT_VALUE = {
        'be_iss_tot_share': None,
        'expo_tot_share': None,
        'expo_issue_fee': None,
        'expo_ratio': None,
        'expo_list_share': None,
        'spon_sub_pla_amt': None,
        'insu_sub_pla_amt': None,
        'pub_sub_pla_amt': None,
        'pub_sub_tot_share': None,
    }
    DATETIME = {
        'announcement_date': None,
        'expo_announcement_date': None,
        'expo_issue_beg_date': None,
        'expo_issue_end_date': None,
        'expo_record_date': None,
        'expo_exright_base_date': None,
        'expo_fund_list_date': None,
    }


class GuaranteedFundInformation(DataTableTransfer):
    """
    基金当事人(TQ_FD_PARTY)/基金费率变动(TQ_FD_RATECHG) --> 保本基金基本信息表(fund_guaranteed_basic_info)
    """
    QUERY_SOURCE = '''select   SECURITYID as sec_uni_code from( 
    select SECURITYID from TQ_FD_RATECHG where RATENAME2=1201  union all 
    select SECURITYID from TQ_FD_PARTY where PARTYTYPE=2) ta;'''

    QUERY_TARGET = '''select replace(b.source_id,'FD','') as sec_uni_code
     from fund_guaranteed_basic_info f join sec_basic_info b on f.sec_uni_code = b.sec_uni_code where  f.status<>'9' ;'''
    # 待插入的数据查询SQL
    # QUERY_SQL = ('''select distinct concat('FD',SECURITYID) as SECURITYID, SECURITYID as SECURITYID1 from (
    # select SECURITYID from TQ_FD_RATECHG where RATENAME2=1201
    # union all
    # select SECURITYID from TQ_FD_PARTY where PARTYTYPE=2) ta limit %s, 1000;''')

    TABLE = 'fund_guaranteed_basic_info'  # 待插入的表名
    SOURCE_ID = {'sec_uni_code':False, }
    COLUMN_ID = 'sec_uni_code'  # 更新主键

    MAP_FIELDS = GuaranteedFundInfoMap
    MAP_VALUES = GuaranteedFundInfoValueMap


class GuaranteedFundCycle(DataTableTransfer):
    """
    基金特殊日期主表(TQ_FD_FUNDSDATE) --> 基金保本周期表(fund_guaranteed_cycle)
    """
    QUERY_SOURCE = '''select   SECURITYID as sec_uni_code,DTYPE as  date_type ,BEGINDATE as begin_date
    from TQ_FD_FUNDSDATE where DTYPE in(5,6);'''

    QUERY_TARGET = '''select replace(b.source_id,'FD','') as sec_uni_code,date_type,replace(f.begin_date,'-','') as begin_date
    from fund_guaranteed_cycle f join sec_basic_info b on f.sec_uni_code = b.sec_uni_code where  f.status<>'9' ;'''
    # 待插入的数据查询SQL
    # QUERY_SQL = ('''select concat('FD',SECURITYID) as SECURITYID,DECLAREDATE,DTYPE,BEGINDATE,ENDDATE,MEMO
    # from TQ_FD_FUNDSDATE where DTYPE in(5,6) limit %s, 1000;''')

    TABLE = 'fund_guaranteed_cycle'  # 待插入的表名
    SOURCE_ID = {'sec_uni_code':False, 'date_type':True, 'begin_date':False}  # 被关联表的主键, 源表与目标表的关联字段
    COLUMN_ID = 'id'  # 更新主键

    DATETIME = {
        'announcement_date': None,
        'begin_date': None,
        'end_date': None,
    }

    MAP_FIELDS = GuaranteedFundCycleMap
    MAP_VALUES = GuaranteedFundCycleValueMap


class ETFBasicInfo(DataTableTransfer):
    """
    基金基本信息(TQ_FD_BASICINFO)(ETF) --> ETF基本资料(fund_etf_basic_info)
    """
    QUERY_SOURCE = '''select   SECURITYID as sec_uni_code from TQ_FD_BASICINFO where FDNATURE='ETF';'''

    QUERY_TARGET = '''select replace(b.source_id,'FD','') as sec_uni_code
         from fund_etf_basic_info f join sec_basic_info b on f.sec_uni_code = b.sec_uni_code where  f.status<>'9' ;'''
    # 待插入的数据查询SQL
    # QUERY_SQL = ('''select concat('FD',SECURITYID) as SECURITYID, SECURITYID as SECURITYID1, LISTDATE
    # from TQ_FD_BASICINFO where FDNATURE='ETF' limit %s, 1000;''')

    TABLE = 'fund_etf_basic_info'  # 待插入的表名
    SOURCE_ID = {'sec_uni_code':False, }  # 被关联表的主键, 源表与目标表的关联字段
    COLUMN_ID = 'sec_uni_code'  # 更新主键

    DATETIME = {
        'list_date': None
    }

    MAP_FIELDS = ETFBasicInfoMap
    MAP_VALUES = ETFBasicInfoValueMap


class LOFBasicInfo(DataTableTransfer):
    """
    基金基本信息(TQ_FD_BASICINFO)(LOF) --> LOF基本资料(fund_lof_basic_info)
    """
    QUERY_SOURCE = '''select   SECURITYID as sec_uni_code from TQ_FD_BASICINFO  where FDNATURE='LOF';'''

    QUERY_TARGET = '''select replace(b.source_id,'FD','') as sec_uni_code
            from fund_lof_basic_info f join sec_basic_info b on f.sec_uni_code = b.sec_uni_code where  f.status<>'9' ;'''
    # 待插入的数据查询SQL
    # QUERY_SQL = ('''select concat('FD',SECURITYID) as SECURITYID, DECLAREDATE, LISTDATE from TQ_FD_BASICINFO
    # where FDNATURE='LOF' limit %s, 1000;''')

    TABLE = 'fund_lof_basic_info'  # 待插入的表名
    SOURCE_ID = {'sec_uni_code':False, }  # 被关联表的主键, 源表与目标表的关联字段
    COLUMN_ID = 'sec_uni_code'  # 更新主键

    DATETIME = {
        'announcement_date': None,
        'list_date': None
    }

    MAP_FIELDS = LOFBasicInfoMap
    MAP_VALUES = LOFBasicInfoValueMap


class MoneyMarketFundBasicInfo(DataTableTransfer):
    """
    基金基本信息(TQ_FD_BASICINFO) --> 货币基金基本资料(fund_money_market_basic_info)
    """
    QUERY_SOURCE = '''select   SECURITYID as sec_uni_code from TQ_FD_BASICINFO where PROFITPAYTYPE IN('1','2','3','4','5') and TRASTYPE!='09';'''

    QUERY_TARGET = '''select replace(b.source_id,'FD','') as sec_uni_code
     from fund_money_market_basic_info f join sec_basic_info b on f.sec_uni_code = b.sec_uni_code where  f.status<>'9' ;'''
    # 待插入的数据查询SQL
    # QUERY_SQL = ('''select concat('FD',SECURITYID) as SECURITYID, DECLAREDATE, PROFITPAYTYPE, TRASTYPE,
    # EMONOVERDATE from TQ_FD_BASICINFO
    # where PROFITPAYTYPE IN('1','2','3','4','5') and TRASTYPE!='09' limit %s, 1000;''')

    TABLE = 'fund_money_market_basic_info'  # 待插入的表名
    SOURCE_ID = {'sec_uni_code':False, }   # 被关联表的主键, 源表与目标表的关联字段
    COLUMN_ID = 'sec_uni_code'  # 更新主键

    DATETIME = {
        'announcement_date': None,
    }

    MAP_FIELDS = MoneyMarketFundBasicInfoMap
    MAP_VALUES = MoneyMarketFundBasicInfoValueMap


if __name__ == '__main__':

    log_path = '../logs/export_fund_info1_server.log'
    logging.basicConfig(filename=log_path,
                        format='%(asctime)s %(name)s %(levelname)s [line %(lineno)s]: %(message)s',
                        filemode='w',
                        level=logging.INFO)

    # InstitutionsCompany.work()
    # InstitutionsFunds.work()
    FundSecurities.work()
    # FundInformation.work()
    # FundBenchmark.work()
    # FundRateInfo.work()
    # ClosedFundInformation.work()
    # GuaranteedFundInformation.work()
    # GuaranteedFundCycle.work()
    # ETFBasicInfo.work()
    # LOFBasicInfo.work()
    # MoneyMarketFundBasicInfo.work()
